Imports WRDB.Controls

Public Class frmExportLSPC

    Private _MDBFile As String
    Private DB As clsDB
    Private dsLSPC As New DataSet
    Private daStations, daPCodes, daDefaults, daPS_Timeseries, daSettings As DbDataAdapter
    Private WithEvents dgvStations, dgvPCodes, dgvDefaults, dgvSummary, dgvModelParm, dgvStationDefaults, dgvCrossTabRaw, dgvCrossTabProc, dgvTimeseries As WRDB.Controls.DGVEditor
    Private lstParm As New Generic.List(Of String)
    Private CrossTabRawNeedsUpdate As Boolean = True
    Private CrossTabProcNeedsUpdate As Boolean = True
    Private CrossTabProcModified As Boolean = False

    Enum enumStatType
        Conc
        Load
    End Enum

    Dim dictStationStats As Generic.Dictionary(Of String, Generic.Dictionary(Of String, clsStats(,)))

    Public Sub New(ByVal MDBFile As String)

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        _MDBFile = MDBFile

        Text = "Export Selected Data to LSPC - " & IO.Path.GetFileName(_MDBFile)

    End Sub

    Private Sub frmExportLSPC_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Try
            SaveWindowPos(REGAPP, Me)
            SaveSettingTbl("Stairstep", chkStairstep.Checked)
            SaveSettingTbl("AddMissing", chkAddMissing.Checked)
            SaveSettingTbl("Multiplier", txtMult.Text)
            SaveSettingTbl("FlowMult", txtFlowMult.Text)
            SaveSettingTbl("LoadMult", txtLoadMult.Text)
            SaveSettingTbl("AddMissing", chkAddMissing.Checked)
            SaveSettingTbl("MaxMissing", txtMaxMissing.Text)
            SaveSettingTbl("chkOutlier", chkOutlier.Checked)
            SaveSettingTbl("Outlier", txtOutlier.Text)
            SaveSettingTbl("FlowPCode", cboFlowPCode.Text)
            SaveSettingTbl("NOrganParm", cboNOrgan.Text)
            SaveSettingTbl("NH3Parm", cboNH3.Text)
            SaveSettingTbl("NOXParm", cboNOX.Text)
            SaveSettingTbl("NTotalParm", cboNTotal.Text)
            SaveSettingTbl("POrganParm", cboPOrgan.Text)
            SaveSettingTbl("POrthoParm", cboPOrtho.Text)
            SaveSettingTbl("PTotalParm", cboPTotal.Text)
            SaveSettingTbl("NOrganPct", txtNOrgan.Text)
            SaveSettingTbl("NH3Pct", txtNH3.Text)
            SaveSettingTbl("NOXPct", txtNOX.Text)
            SaveSettingTbl("POrganPct", txtPOrgan.Text)
            SaveSettingTbl("POrthoPct", txtPOrtho.Text)
            If dsLSPC.Tables.Contains("WRDB_Settings") Then daSettings.Update(dsLSPC.Tables("WRDB_Settings"))
        Catch ex As Exception
            ErrorMsg("Unable to update Access database table; please close tool and reopen to try again.", ex)
        End Try
    End Sub

    Private Sub SaveSettingTbl(ByVal Name As String, ByVal Value As String)
        Try
            If Not dsLSPC.Tables.Contains("WRDB_Settings") Then Exit Sub
            With dsLSPC.Tables("WRDB_Settings")
                If .Rows.Count > 0 Then
                    Dim r As DataRow = .Rows.Find(Name)
                    If r Is Nothing Then
                        .Rows.Add(Name, Value)
                    Else
                        r.Item("SettingValue") = Value
                    End If
                Else
                    .Rows.Add(Name, Value)
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Function GetSettingTbl(ByVal Name As String, ByVal DefaultValue As String) As String
        Try
            With dsLSPC.Tables("WRDB_Settings")
                Dim r As DataRow = .Rows.Find(Name)
                If r Is Nothing Then
                    Return DefaultValue
                Else
                    Return r.Item("SettingValue")
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
            Return Nothing
        End Try
    End Function

    Private Sub frmExportLSPC_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
        Static AlreadyShown As Boolean = False
        If Not AlreadyShown Then frmInfoMsg.ShowInfoMsg("Export to LSPC", "Recent enhancements to the LSPC export tool include a more sophisticated set of rules deciding how speciation is handled when observed data are missing but monthly average data are available.")
        AlreadyShown = True
    End Sub

    Private Sub frmExportLSPC_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            GetWindowPos(REGAPP, Me)
            DB = clsDBOleDB.GetDataProvider(clsDB.enumDBType.Access, _MDBFile)

            dgvStations = New WRDB.Controls.DGVEditor(dgStations)
            dgvPCodes = New WRDB.Controls.DGVEditor(dgPCodes)
            dgvDefaults = New WRDB.Controls.DGVEditor(dgDefaults)
            dgvSummary = New WRDB.Controls.DGVEditor(dgSummary)
            dgvModelParm = New WRDB.Controls.DGVEditor(dgModelParm)
            dgvStationDefaults = New WRDB.Controls.DGVEditor(dgStationStats)
            dgvCrossTabRaw = New WRDB.Controls.DGVEditor(dgCrosstabRaw)
            dgvCrossTabProc = New WRDB.Controls.DGVEditor(dgCrosstabProc)
            dgvTimeseries = New WRDB.Controls.DGVEditor(dgTimeseries)

            With DB
                .CreateConnection()

                MainForm.WriteProgress("Opening tables in LSPC database...", ProgressBarStyle.Marquee)
                Application.DoEvents()

                .AddTable(dsLSPC, "ModelParm")
                .AddTable(dsLSPC, "Watershed_Physical")

                Application.DoEvents()

                If Not .TableExists(New String() {"WRDB_Stations", "WRDB_PCodes", "WRDB_Defaults", "WRDB_Settings"}) Then
                    If Not Reset() Then Close() : Exit Sub
                End If

                Application.DoEvents()

                With DB
                    Try
                        .DropTable("WRDB_PS_Timeseries")
                    Catch ex As Exception
                        ErrorMsg("Unable to access LSPC database: " & _MDBFile & "; it may already be open by another application.", ex)
                        Throw New ApplicationException("LSPC Database Unavailable")
                        Exit Sub
                    End Try
                    .ExecuteNonQuery("CREATE TABLE WRDB_PS_Timeseries (SubBasin INTEGER, Permit VARCHAR(255), Pipe VARCHAR(255), Parm VARCHAR(50), Name VARCHAR(255), Flow DOUBLE, Load DOUBLE, [Date] DATETIME)")
                End With

                daStations = .AddTable(dsLSPC, "WRDB_Stations")
                daPCodes = .AddTable(dsLSPC, "WRDB_PCodes")
                daDefaults = .AddTable(dsLSPC, "WRDB_Defaults")
                daPS_Timeseries = .AddTable(dsLSPC, "WRDB_PS_Timeseries")
                daSettings = .AddTable(dsLSPC, "WRDB_Settings")
                .Connection.Close()
                .Dispose()
            End With

            With dgStations
                With dsLSPC.Tables("WRDB_Stations")
                    .PrimaryKey = New DataColumn() {.Columns(0)}
                End With
                Dim bs As New BindingSource(dsLSPC, "WRDB_Stations")
                .DataSource = bs

                'make list comprised of all station ids in table, but put those in the currently selected table at the top

                Dim Lst As Generic.List(Of String) = Project.DB.GetRecordList(clsProject.TempData, "Station_ID")
                Lst.Add("--------------------------")
                For Each s As String In Project.DB.GetRecordList("Stations", "Station_ID")
                    If Not Lst.Contains(s) Then Lst.Add(s)
                Next
                dgvStations.SetColumnFormat(WRDB.Controls.DGVEditor.enumColumnTypes.ComboBox, 0, Lst)
                dgvStations.SetColumnFormat(WRDB.Controls.DGVEditor.enumColumnTypes.ComboBox, 1, DB.GetRecordList("Watershed_Physical", "SubBasin"))
                .AllowUserToAddRows = True
                .AllowUserToDeleteRows = True
                .ShowRowErrors = False
                .Columns(0).Frozen = True
                .Columns(1).Frozen = True
            End With

            With dgPCodes
                With dsLSPC.Tables("WRDB_PCodes")
                    .PrimaryKey = New DataColumn() {.Columns(0)}
                End With
                Dim bs As New BindingSource(dsLSPC, "WRDB_PCodes")
                .DataSource = bs
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells

                Dim Lst As Generic.List(Of String) = Project.DB.GetRecordList(clsProject.TempData, "PCode")
                Lst.Add("--------------------------")
                For Each s As String In Project.DB.GetRecordList("PCodes", "PCode")
                    If Not Lst.Contains(s) Then Lst.Add(s)
                Next
                cboFlowPCode.Items.AddRange(Lst.ToArray)
                dgvPCodes.SetColumnFormat(WRDB.Controls.DGVEditor.enumColumnTypes.ComboBox, 1, Lst)
                dgvPCodes.SetColumnFormat(WRDB.Controls.DGVEditor.enumColumnTypes.ComboBox, 2, Lst)
                .AllowUserToAddRows = True
                .AllowUserToDeleteRows = True
                For Each col As DataGridViewColumn In .Columns
                    col.SortMode = DataGridViewColumnSortMode.NotSortable
                Next
            End With

            With dgDefaults
                With dsLSPC.Tables("WRDB_Defaults")
                    .PrimaryKey = New DataColumn() {.Columns(0)}
                End With
                Dim bs As New BindingSource(dsLSPC, "WRDB_Defaults")
                .DataSource = bs
                For Each col As DataGridViewColumn In .Columns
                    col.SortMode = DataGridViewColumnSortMode.NotSortable
                    If col.HeaderText = "Parm" Then
                        col.ReadOnly = True
                        col.Frozen = True
                    Else
                        col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
                        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                        col.ValueType = GetType(Double)
                    End If
                Next
            End With

            With dgModelParm
                Dim bs As New BindingSource(dsLSPC, "ModelParm")
                .DataSource = bs
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .ReadOnly = True
            End With

            MainForm.OutputReport(frmMain.enumReportType.SumStaPCode, True)
            Project.DB.AddTable(dsLSPC, clsProject.RptData)
            With dgSummary
                Dim bs As New BindingSource(dsLSPC, clsProject.RptData)
                .DataSource = bs
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
                .ReadOnly = True
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                For c As Integer = 0 To .ColumnCount - 1
                    With .Columns(c)
                        .SortMode = DataGridViewColumnSortMode.NotSortable
                        Select Case c
                            Case 5
                                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                                .DefaultCellStyle.Format = "N0"
                            Case 6, 7, 8
                                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                                .DefaultCellStyle.Format = "N3"
                            Case 9, 10
                                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                                .DefaultCellStyle.Format = MyDateFormat()
                            Case Else
                                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                        End Select
                    End With
                Next
            End With

            With dgTimeseries
                Dim bs As New BindingSource(dsLSPC, "WRDB_PS_Timeseries")
                .DataSource = bs
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
                .AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
            End With

            lstParm.Clear()
            lstParm.AddRange(DB.GetRecordList("ModelParm", "LABEL").ToArray)
            For i As Integer = 1 To 7
                Dim cbo As ComboBox = Choose(i, cboNOrgan, cboNH3, cboNOX, cboNTotal, cboPOrgan, cboPOrtho, cboPTotal)
                cbo.Items.AddRange(lstParm.ToArray)
            Next

            With dsLSPC.Tables("WRDB_Settings")
                .PrimaryKey = New DataColumn() {.Columns(0)}
                Try
                    chkStairstep.Checked = GetSettingTbl("Stairstep", True)
                    chkAddMissing.Checked = GetSettingTbl("AddMissing", True)
                    txtMult.Text = GetSettingTbl("Multiplier", 3.7854)
                    txtFlowMult.Text = GetSettingTbl("FlowMult", 1.5472)
                    txtLoadMult.Text = GetSettingTbl("LoadMult", 0.4087)
                    chkAddMissing.Checked = GetSettingTbl("AddMissing", False)
                    txtMaxMissing.Text = GetSettingTbl("MaxMissing", 3)
                    chkOutlier.Checked = GetSettingTbl("chkOutlier", True)
                    txtOutlier.Text = GetSettingTbl("Outlier", 3)
                    cboNOrgan.Text = GetSettingTbl("NOrganParm", "ORG-N")
                    cboFlowPCode.Text = GetSettingTbl("FlowPCode", "FLOWMGD")
                    cboNH3.Text = GetSettingTbl("NH3Parm", "NH3")
                    cboNOX.Text = GetSettingTbl("NOXParm", "NOX")
                    cboNTotal.Text = GetSettingTbl("NTotalParm", "TN")
                    cboPOrgan.Text = GetSettingTbl("POrganParm", "ORG-P")
                    cboPOrtho.Text = GetSettingTbl("POrthoParm", "SRP")
                    cboPTotal.Text = GetSettingTbl("PTotalParm", "TP")
                    txtNOrgan.Text = GetSettingTbl("NOrganPct", 14)
                    txtNH3.Text = GetSettingTbl("NH3Pct", 14)
                    txtNOX.Text = GetSettingTbl("NOXPct", 72)
                    txtPOrgan.Text = GetSettingTbl("POrganPct", 25)
                    txtPOrtho.Text = GetSettingTbl("POrthoPct", 75)
                Catch ex As Exception
                End Try
            End With

            Dim dr As DbDataReader = Project.DB.ExecuteReader("SELECT MIN(Date_Time) as MinDate,MAX(Date_Time) as MaxDate FROM {0}", clsProject.TempData)
            If dr IsNot Nothing And dr.Read Then
                dtStartDate.CustomFormat = MyDateFormat()
                dtStartDate.Value = dr.GetDateTime(0)
                dtEndDate.CustomFormat = MyDateFormat()
                dtEndDate.Value = dr.GetDateTime(1)
            End If
            dr.Close()

        Catch ex As Exception
            If ex.Message.StartsWith("LSPC") Then Throw New ApplicationException Else ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Function Reset() As Boolean
        Try
            MainForm.WriteProgress("Resetting tables in LSPC database...", ProgressBarStyle.Marquee)
            With DB
                .DropTable("WRDB_Stations")
                Dim sql As String = "CREATE TABLE WRDB_Stations (Station_ID VARCHAR(25), SubBasin VARCHAR(10)"
                Dim lstParms As New Generic.List(Of String)
                For Each r As DataRow In dsLSPC.Tables("ModelParm").Rows
                    Dim parm As String = r.Item("LABEL").ToString.Replace("-", "_").Replace(" ", "_")
                    lstParms.Add(parm)
                    sql &= String.Format(", {0} LOGICAL", parm)
                Next
                sql &= ", PRIMARY KEY (Station_ID))"
                .ExecuteNonQuery(sql)
                Application.DoEvents()

                .DropTable("WRDB_PCodes")
                .ExecuteNonQuery("CREATE TABLE WRDB_PCodes (Parm VARCHAR(50),PCode_Conc VARCHAR(10),PCode_Load VARCHAR(10), PRIMARY KEY (Parm))")
                Application.DoEvents()

                .DropTable("WRDB_Defaults")

                'note: for some reason, cannot create and update if field name is DEC, even if format at [Dec]

                .ExecuteNonQuery("CREATE TABLE WRDB_Defaults (Parm VARCHAR(50), January DOUBLE, February DOUBLE, March DOUBLE, April DOUBLE, May DOUBLE, June DOUBLE, July DOUBLE, August DOUBLE, September DOUBLE, October DOUBLE, November DOUBLE, December DOUBLE, PRIMARY KEY (Parm))")
                Application.DoEvents()

                .DropTable("WRDB_PS_Timeseries")
                .ExecuteNonQuery("CREATE TABLE WRDB_PS_Timeseries (SubBasin INTEGER, Permit VARCHAR(255), Pipe VARCHAR(255), Parm VARCHAR(50), Name VARCHAR(255), Flow DOUBLE, Load DOUBLE, [Date] DATETIME)")
                Application.DoEvents()

                .DropTable("WRDB_Settings")
                .ExecuteNonQuery("CREATE TABLE WRDB_Settings (SettingName VARCHAR(25), SettingValue VARCHAR(255), PRIMARY KEY (SettingName))")
                Application.DoEvents()

                If dsLSPC.Tables.Contains("WRDB_Stations") Then dsLSPC.Tables.Remove("WRDB_Stations")
                If dsLSPC.Tables.Contains("WRDB_PCodes") Then dsLSPC.Tables.Remove("WRDB_PCodes")
                If dsLSPC.Tables.Contains("WRDB_Defaults") Then dsLSPC.Tables.Remove("WRDB_Defaults")
                If dsLSPC.Tables.Contains("WRDB_Settings") Then dsLSPC.Tables.Remove("WRDB_Settings")

                daStations = .AddTable(dsLSPC, "WRDB_Stations")
                daPCodes = .AddTable(dsLSPC, "WRDB_PCodes")
                daDefaults = .AddTable(dsLSPC, "WRDB_Defaults")
                daSettings = .AddTable(dsLSPC, "WRDB_Settings")

                With dsLSPC.Tables("WRDB_Stations") : .PrimaryKey = New DataColumn() {.Columns(0)} : End With
                With dsLSPC.Tables("WRDB_PCodes") : .PrimaryKey = New DataColumn() {.Columns(0)} : End With
                With dsLSPC.Tables("WRDB_Defaults") : .PrimaryKey = New DataColumn() {.Columns(0)} : End With
                With dsLSPC.Tables("WRDB_Settings") : .PrimaryKey = New DataColumn() {.Columns(0)} : End With

                Dim bs As New BindingSource(dsLSPC, "WRDB_PCodes")
                dgPCodes.DataSource = bs

                With dsLSPC.Tables("WRDB_PCodes")
                    For Each s As String In DB.GetRecordList("ModelParm", "LABEL")
                        .Rows.Add(s, DBNull.Value, DBNull.Value)
                    Next
                    Dim numrows As Integer = daPCodes.Update(dsLSPC.Tables("WRDB_PCodes"))
                    .AcceptChanges()
                End With

                bs = New BindingSource(dsLSPC, "WRDB_Defaults")
                dgDefaults.DataSource = bs

                With dsLSPC.Tables("WRDB_Defaults")
                    .Rows.Add("Flow", 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100)
                    For Each s As String In DB.GetRecordList("ModelParm", "LABEL")
                        If s.ToUpper <> "FLOW" Then .Rows.Add(s, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100)
                    Next
                    Dim numrows As Integer = daDefaults.Update(dsLSPC.Tables("WRDB_Defaults"))
                    .AcceptChanges()
                End With

                With dsLSPC.Tables("WRDB_Stations")
                    Dim numrows As Integer = daStations.Update(dsLSPC.Tables("WRDB_Stations"))
                    .AcceptChanges()
                End With

                With dsLSPC.Tables("WRDB_Settings")
                    .PrimaryKey = New DataColumn() {.Columns(0)}
                End With

                dgCrosstabRaw.Rows.Clear()
                dgCrosstabProc.Rows.Clear()
                dgStations.DataSource = dsLSPC.Tables("WRDB_Stations")
                dgPCodes.DataSource = dsLSPC.Tables("WRDB_PCodes")
                dgDefaults.DataSource = dsLSPC.Tables("WRDB_Defaults")
                dgTimeseries.DataSource = dsLSPC.Tables("WRDB_PS_TimeSeries")
            End With
            Return True
        Catch ex As Exception
            ErrorMsg("Unable to update Access database table; please close tool and reopen to try again.", ex)
            Return False
        Finally
            MainForm.ProgressComplete()
        End Try
    End Function

    Private Sub btnPreview_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPreview.Click
        Try
            If dgCrosstabProc.RowCount > 0 AndAlso CrossTabProcModified AndAlso WarningMsg("Some settings have changed requiring a regeneration of the crosstab. This will cause you to lose any edits you may have made. Are you sure you want to continue?") = Windows.Forms.DialogResult.Cancel Then Exit Sub

            If Not CreateCrossTabProc() Then Exit Sub

        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Function CreateCrossTabRaw() As Boolean
        Dim dr As DbDataReader = Nothing
        Try
            Try
                'do cross-tab report...

                MainForm.WriteProgress("Preparing raw data crosstab...", ProgressBarStyle.Marquee)
                Application.UseWaitCursor = True
                TabMain.Enabled = False

                'create dictionaries so can quickly substitute mapped values
                Dim dictStations As New Generic.Dictionary(Of String, Integer)
                For Each r As DataRow In dsLSPC.Tables("WRDB_Stations").Rows
                    Dim StationID As Object = r.Item("Station_ID")
                    If IsDBNull(StationID) OrElse String.IsNullOrEmpty(StationID) OrElse dictStations.ContainsKey(StationID) Then
                        WarningMsg("Each row on the Stations tab must have a unique Station ID entered.")
                        Return False
                    ElseIf Not IsDBNull(r.Item("Subbasin")) Then
                        dictStations.Add(r.Item("Station_ID"), r.Item("Subbasin"))
                    End If
                Next

                Dim dictPCodes As New Generic.Dictionary(Of String, String)
                For Each r As DataRow In dsLSPC.Tables("WRDB_PCodes").Rows
                    Dim PCodeConc As Object = r.Item("PCode_Conc")
                    If Not (IsDBNull(PCodeConc) OrElse String.IsNullOrEmpty(PCodeConc)) Then
                        If dictPCodes.ContainsKey(PCodeConc) Then
                            WarningMsg("Each non-empty row on the PCodes tab must have a unique PCode entered.")
                            Return False
                        Else
                            dictPCodes.Add(PCodeConc, r.Item("Parm") & "-Conc")
                        End If
                    End If
                    Dim PCodeLoad As Object = r.Item("PCode_Load")
                    If Not (IsDBNull(PCodeLoad) OrElse String.IsNullOrEmpty(PCodeLoad)) Then
                        If dictPCodes.ContainsKey(PCodeLoad) Then
                            WarningMsg("Each non-empty row on the PCodes tab must have a unique PCode entered.")
                            Return False
                        Else
                            dictPCodes.Add(PCodeLoad, r.Item("Parm") & "-Load")
                        End If
                    End If
                Next

                'get unique pcodes for column headings and save as datatable columns

                With dgCrosstabRaw
                    .SuspendLayout()
                    .Visible = False
                    .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
                    .AllowUserToAddRows = True
                    .AllowUserToDeleteRows = True
                    .AllowUserToResizeColumns = False
                    .AllowUserToResizeRows = False
                    .Rows.Clear()
                    .Columns.Clear()
                    .ColumnCount = dsLSPC.Tables("WRDB_PCodes").Rows.Count * 2 + 6
                    For i As Integer = 0 To 5
                        With .Columns(i)
                            .HeaderText = Choose(i + 1, "SubBasin", "Station_ID", "Permit", "Outfall", "Date_Time", "Flow")
                            .Name = .HeaderText
                            Select Case i
                                Case 4
                                    .ValueType = GetType(Date)
                                    .DefaultCellStyle.Format = MyDateFormat()
                                    .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                                Case 5
                                    .ValueType = GetType(Double)
                                    .DefaultCellStyle.Format = "N2"
                                    .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                                Case Else
                                    .ValueType = GetType(String)
                                    .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                            End Select
                        End With
                    Next
                    Dim c As Integer = 6
                    For r As Integer = 0 To dsLSPC.Tables("WRDB_PCodes").Rows.Count - 1
                        Dim Parm As String = dsLSPC.Tables("WRDB_PCodes").Rows(r).Item("Parm")
                        For i As Integer = 1 To 2
                            With .Columns(c)
                                .HeaderText = Parm & Choose(i, "-Conc", "-Load")
                                .Name = .HeaderText
                                .ValueType = GetType(Double)
                                .DefaultCellStyle.Format = "N3"
                            End With
                            c += 1
                        Next
                    Next

                    'the Station_ID field is just a convenience; hide it
                    .Columns("Station_ID").Visible = False

                    'turn this off while doing calcs
                    .AllowUserToAddRows = False

                    'query data for all observations

                    Dim Sql As String

                    'form list of station IDs requested
                    Dim StationList As String = ""
                    For Each kv As Generic.KeyValuePair(Of String, Integer) In dictStations
                        If StationList <> "" Then StationList &= ","
                        StationList &= "'" & kv.Key & "'"
                    Next

                    If chkStairstep.Checked Then 'force only one value per month; assign to first day of month
                        Sql = String.Format("Select Station_ID, {3}, PCode, ROUND(AVG(Result),8) FROM {0} WHERE Date_Time BETWEEN {1} AND {2} AND Station_ID IN ({5}) GROUP BY Station_ID, {4}, PCode ORDER BY Station_ID, {4}, PCode", clsProject.TempData, Project.DB.DateTime(dtStartDate.Value), Project.DB.DateTime(dtEndDate.Value), Project.DB.DateFormat("Date_Time", clsDB.enumDateFormat.MM01YYYY), Project.DB.DateFormat("Date_Time", clsDB.enumDateFormat.YYYYMM), StationList)
                    Else 'just take the data at whatever date they occur on
                        Sql = String.Format("Select Station_ID, Date_Time, PCode, ROUND(AVG(Result),8) FROM {0} WHERE Date_Time BETWEEN {1} AND {2} AND Station_ID IN ({3}) GROUP BY Station_ID, Date_Time, PCode ORDER BY Station_ID, Date_Time, PCode", clsProject.TempData, Project.DB.DateTime(dtStartDate.Value), Project.DB.DateTime(dtEndDate.Value), StationList)
                    End If

                    Dim SqlCount As String = String.Format("Select COUNT(*) FROM ({0}) AS CNT", Sql)
                    Dim NumRec As Integer = Project.DB.ExecuteScalar(SqlCount)

                    dr = Project.DB.ExecuteReader(Sql)
                    If dr Is Nothing Then Exit Function
                    Dim lastrow As String = ""
                    Dim recnum As Integer = 0
                    While dr.Read
                        Dim StationID As String = dr.GetString(0)
                        Dim DateTime As Date = dr.GetValue(1)
                        If dictStations.ContainsKey(StationID) Then

                            'Debug.Print(StationID & "," & DateTime & "," & dr.GetValue(2) & "," & dr.GetValue(3))

                            Dim Subbasin As String = dictStations(StationID)
                            Dim Permit As String = StationID
                            Dim Outfall As String = ""
                            If StationID.Contains("+") Then
                                Permit = StationID.Split("+")(0)
                                Outfall = StationID.Split("+")(1)
                            Else
                                Outfall = "1"
                            End If

                            'fill out prior timeseries at end unless no prior one
                            Dim lastdatarow As DataGridViewRow = Nothing
                            Dim lastStationID As String = ""
                            If .RowCount > 0 Then
                                lastdatarow = .Rows(.RowCount - 1)
                                lastStationID = lastdatarow.Cells("Station_ID").Value
                            End If

                            If StationID <> lastStationID And chkStairstep.Checked And chkAddMissing.Checked Then
                                'if is first record for this station id; fill in end of prior series, and start of this series
                                If .RowCount > 0 Then
                                    Dim dt As Date = lastdatarow.Cells("Date_Time").Value
                                    While dt <= dtEndDate.Value.AddMonths(-1)
                                        dt = dt.AddMonths(1)
                                        .Rows.Add(lastdatarow.Cells("Subbasin").Value, lastdatarow.Cells("Station_ID").Value, lastdatarow.Cells("Permit").Value, lastdatarow.Cells("Outfall").Value, dt.ToString(MyDateTimeFormat))
                                    End While
                                End If
                                'fill out the beginning of this new series if different than last one or first one
                                If .RowCount = 0 Then
                                    Dim dt As Date = DateTime
                                    Dim insertrow As Integer = .RowCount
                                    While dt >= dtStartDate.Value.AddMonths(1)
                                        dt = dt.AddMonths(-1)
                                        .Rows.Insert(insertrow, Subbasin, StationID, Permit, Outfall, dt.ToString(MyDateTimeFormat))
                                    End While
                                End If
                            End If

                            'beginning and ends cleaned up, so write this record

                            Dim rowheading As String = String.Format("{0}   {1}", StationID, DateTime)
                            If rowheading <> lastrow Then 'have gotten all values for prior row, so write new one
                                .Rows.Add(Subbasin, StationID, Permit, Outfall, DateTime.ToString(MyDateTimeFormat))
                            End If
                            Dim PCode As String = dr.GetString(2).Replace(" ", "_").Replace(",", "_")
                            If PCode = cboFlowPCode.Text Then .Rows(.RowCount - 1).Cells("Flow").Value = dr.GetValue(3)
                            If dictPCodes.ContainsKey(PCode) Then .Rows(.RowCount - 1).Cells(dictPCodes(PCode)).Value = dr.GetValue(3)
                            lastrow = rowheading
                            recnum += 1
                            If Not MainForm.WriteProgress(recnum, NumRec, "Creating crosstab...", ProgressBarStyle.Blocks, True) Then Exit Function
                        End If
                    End While

                    'fill in end of prior series
                    If .RowCount > 0 And chkStairstep.Checked And chkAddMissing.Checked Then
                        Dim lastdatarow As DataGridViewRow = .Rows(.RowCount - 1)
                        Dim lastStationID As String = lastdatarow.Cells("Permit").Value
                        Dim dt As Date = lastdatarow.Cells("Date_Time").Value
                        While dt <= dtEndDate.Value.AddMonths(-1)
                            dt = dt.AddMonths(1)
                            .Rows.Add(lastdatarow.Cells("Subbasin").Value, lastdatarow.Cells("Station_ID").Value, lastdatarow.Cells("Permit").Value, lastdatarow.Cells("Outfall").Value, dt.ToString(MyDateTimeFormat))
                        End While
                    End If

                    'make another pass and open up spaces where missing data appear within series
                    If chkStairstep.Checked And chkAddMissing.Checked Then
                        For r As Integer = .RowCount - 1 To 1 Step -1
                            If .Item("Station_ID", r).Value = .Item("Station_ID", r - 1).Value Then
                                Dim dt As Date = .Item("Date_Time", r).Value
                                Dim priordate As Date = .Item("Date_Time", r - 1).Value
                                While dt > priordate.AddMonths(1)
                                    dt = dt.AddMonths(-1)
                                    .Rows.Insert(r, .Item("Subbasin", r).Value, .Item("Station_ID", r).Value, .Item("Permit", r).Value, .Item("Outfall", r).Value, dt.ToString(MyDateTimeFormat))
                                End While
                            End If
                        Next
                    End If

                    .AllowUserToAddRows = True

                    .Columns(3).DefaultCellStyle.Format = MyDateFormat()
                    For i As Integer = 0 To .Columns.Count - 1
                        .Columns(i).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
                        .Columns(i).SortMode = DataGridViewColumnSortMode.NotSortable
                        If i >= 4 Then
                            .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                            .Columns(i).DefaultCellStyle.Format = "N3"
                        End If
                        MainForm.WriteProgress(i, .Columns.Count - 1, "Preparing table...", ProgressBarStyle.Blocks)
                    Next
                    .ReadOnly = False
                    .Columns(0).Frozen = True
                    .Columns(1).Frozen = True
                    .Columns(2).Frozen = True
                    .Columns(3).Frozen = True
                    .Columns(4).Frozen = True

                    'hide columns for which no stations have selected that parameter
                    For cc As Integer = 6 To .ColumnCount - 1
                        .Columns(cc).Visible = False
                    Next
                    For cc As Integer = 2 To dgStations.ColumnCount - 1
                        Dim isChecked As Boolean = False
                        For r As Integer = 0 To dgStations.RowCount - 1
                            If TestNull(dgStations.Item(cc, r).Value, False) Then isChecked = True : Exit For
                        Next
                        If isChecked Then
                            For ccc As Integer = 5 To .ColumnCount - 1
                                If .Columns(ccc).HeaderText.Replace("-", "_").Replace(" ", "_").StartsWith(dgStations.Columns(cc).HeaderText) Then .Columns(ccc).Visible = True
                            Next
                        End If
                    Next
                    .Visible = True
                    .ResumeLayout(True)
                    .AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
                    .FirstDisplayedScrollingRowIndex = 0

                    CrossTabRawNeedsUpdate = False
                    CrossTabProcNeedsUpdate = True
                End With

                'calculate station statistics using data in raw crosstab
                CalcStationStats()

                Return True
            Catch ex As Exception
                ErrorMsg(, ex)
                Return False
            Finally
                TabMain.Enabled = True
                MainForm.ProgressComplete()
            End Try
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            dgSummary.Refresh()
            If dr IsNot Nothing Then
                dr.Close()
                dr.Dispose()
            End If
        End Try
    End Function

    ''' <summary>
    ''' Create the processed crosstab using data from the raw cross tab
    ''' </summary>
    ''' <remarks></remarks>
    Private Function CreateCrossTabProc() As Boolean
        Try

            MainForm.WriteProgress("Preparing processed data crosstab...", ProgressBarStyle.Marquee)

            Application.UseWaitCursor = True
            TabMain.Enabled = False
            If CrossTabRawNeedsUpdate Then CreateCrossTabRaw()
            With dgCrosstabProc
                .SuspendLayout()
                .Visible = False
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
                .AllowUserToAddRows = True
                .AllowUserToDeleteRows = True
                .AllowUserToResizeColumns = False
                .AllowUserToResizeRows = False
                .Rows.Clear()
                .Columns.Clear()

                For Each col As DataGridViewColumn In dgCrosstabRaw.Columns
                    dgCrosstabProc.Columns.Add(col.Clone)
                Next
                For r As Integer = 0 To dgCrosstabRaw.RowCount - 2 'don't include last "add" row
                    .Rows.Add()
                    For c As Integer = 0 To dgCrosstabRaw.ColumnCount - 1
                        .Item(c, r).Value = dgCrosstabRaw.Item(c, r).Value
                    Next
                Next

                .Columns(3).DefaultCellStyle.Format = MyDateFormat()
                For i As Integer = 0 To .Columns.Count - 1
                    .Columns(i).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
                    .Columns(i).SortMode = DataGridViewColumnSortMode.NotSortable
                    If i >= 5 Then
                        .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                        .Columns(i).DefaultCellStyle.Format = "N3"
                    End If
                    MainForm.WriteProgress(i, .Columns.Count - 1, "Preparing table...", ProgressBarStyle.Blocks)
                Next
                .ReadOnly = False
                .Columns(0).Frozen = True
                .Columns(1).Frozen = True
                .Columns(2).Frozen = True
                .Columns(3).Frozen = True
                .Columns(4).Frozen = True
                .Columns(5).Frozen = True

                'the Station_ID field is just a convenience; hide it
                .Columns("Station_ID").Visible = False

                'hide columns for which no stations have selected that parameter
                For c As Integer = 6 To .ColumnCount - 1
                    .Columns(c).Visible = False
                Next
                For c As Integer = 2 To dgStations.ColumnCount - 1
                    Dim isChecked As Boolean = False
                    For r As Integer = 0 To dgStations.RowCount - 1
                        If TestNull(dgStations.Item(c, r).Value, False) Then isChecked = True : Exit For
                    Next
                    If isChecked Then
                        For cc As Integer = 5 To .ColumnCount - 1
                            If .Columns(cc).HeaderText.Replace("-", "_").Replace(" ", "_").StartsWith(dgStations.Columns(c).HeaderText) Then .Columns(cc).Visible = True
                        Next
                    End If
                Next
                ApplyTableStyles()
                .Visible = True
                .ResumeLayout(True)
                .AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
                .FirstDisplayedScrollingRowIndex = 0
            End With
            CrossTabProcNeedsUpdate = False
            CrossTabProcModified = False
            Return True
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        Finally
            TabMain.Enabled = True
            Application.UseWaitCursor = False
            MainForm.ProgressComplete()
        End Try
    End Function

    ''' <summary>
    ''' This will highlight the crosstab grid for missing data, etc., and will also do estimating
    ''' </summary>
    Private Sub ApplyTableStyles()

        Dim sw As IO.StreamWriter = Nothing
        Try
            lnkWarnings.Visible = False
            lblOutliersFound.Visible = False

            If chkAddMissing.Checked Then
                If Not My.Computer.FileSystem.DirectoryExists(clsProject.TempPath) Then My.Computer.FileSystem.CreateDirectory(clsProject.TempPath)
                sw = New IO.StreamWriter(clsProject.TempPath & "\LSPC Export.txt")
                sw.WriteLine("Warnings and errors associated with LSPC export operation performed on " & Now & vbCrLf)
            End If

            Dim some_outliers As Boolean = False 'will set to true if some values determined to be outliers

            With dgCrosstabProc
                Dim styNormal As New DataGridViewCellStyle(.DefaultCellStyle) : styNormal.Alignment = DataGridViewContentAlignment.MiddleRight : styNormal.Format = "N3"
                Dim styAllGood As New DataGridViewCellStyle(styNormal) : styAllGood.BackColor = Color.LightGreen
                Dim styMissing As New DataGridViewCellStyle(styNormal) : styMissing.BackColor = Color.Red : styMissing.Font = New Font(styMissing.Font, FontStyle.Bold)
                Dim styNoConc As New DataGridViewCellStyle(styNormal) : styNoConc.BackColor = Color.Pink
                Dim styAverage As New DataGridViewCellStyle(styNormal) : styAverage.BackColor = Color.LightGray : styAverage.Font = New Font(styAverage.Font, FontStyle.Italic)
                Dim styComputed As New DataGridViewCellStyle(styNormal) : styComputed.BackColor = Color.CornflowerBlue : styComputed.Font = New Font(styAverage.Font, FontStyle.Italic)
                Dim styMonthly As New DataGridViewCellStyle(styNormal) : styMonthly.BackColor = Color.Gold : styMonthly.Font = New Font(styAverage.Font, FontStyle.Italic)
                Dim styDefaults As New DataGridViewCellStyle(styNormal) : styDefaults.BackColor = Color.Goldenrod : styDefaults.Font = New Font(styAverage.Font, FontStyle.Italic Or FontStyle.Underline)
                Dim stySpeciation As New DataGridViewCellStyle(styNormal) : stySpeciation.BackColor = Color.Cyan : stySpeciation.Font = New Font(styAverage.Font, FontStyle.Italic)
                Dim styNoFlow As New DataGridViewCellStyle() : styNoFlow.BackColor = Color.LightCoral
                Dim styZeroFlow As New DataGridViewCellStyle() : styZeroFlow.BackColor = Color.PaleVioletRed
                'Dim styFlowCol As New DataGridViewCellStyle(styNormal) : styFlowCol.BackColor = Color.LightGoldenrodYellow
                Dim styOutlier As New DataGridViewCellStyle(styNormal) : styOutlier.BackColor = Color.DarkRed : styOutlier.ForeColor = Color.Yellow : styOutlier.Font = New Font(styAverage.Font, FontStyle.Bold)

                With lblLegendFound : .BackColor = styAllGood.BackColor : .Visible = False : End With
                With lblLegendMissing : .BackColor = styMissing.BackColor : .Visible = False : End With
                With lblLegendComputed : .BackColor = styComputed.BackColor : .Font = styComputed.Font : .Visible = False : End With
                With lblLegendAverage : .BackColor = styAverage.BackColor : .Font = styAverage.Font : .Visible = False : End With
                With lblLegendDefaults : .BackColor = styDefaults.BackColor : .Font = styDefaults.Font : .Visible = False : End With
                With lblLegendMonthly : .BackColor = styMonthly.BackColor : .Visible = False : End With
                With lblLegendNoFlow : .BackColor = styNoFlow.BackColor : .Visible = False : End With
                With lblLegendNoConc : .BackColor = styNoConc.BackColor : .Visible = False : End With
                With lblLegendZeroFlow : .BackColor = styZeroFlow.BackColor : .Visible = False : End With
                With lblLegendOutlier : .BackColor = styOutlier.BackColor : .Visible = False : End With
                With lblLegendOutlier : .ForeColor = styOutlier.ForeColor : .Font = styOutlier.Font : .Visible = False : End With
                With lblLegendSpeciation : .BackColor = stySpeciation.BackColor : .Font = stySpeciation.Font : .Visible = False : End With

                Dim Mult As Double = Val(txtMult.Text)
                Dim FlowColumn As Integer = 5

                'highlight cells where data are supposed to be entered; this relies on columns in cross tab and station list matching
                'also compute missing concentrations

                For r As Integer = 0 To .RowCount - 2
                    Dim StationID As String = .Item("Station_ID", r).Value
                    Dim foundStationRow As DataRow = dsLSPC.Tables("WRDB_Stations").Rows.Find(StationID)
                    Dim DateTime As Date = CDate(.Item(4, r).Value)
                    Dim flow As Double = Val(TestNull(.Item(FlowColumn, r).Value, -1))

                    'fix up concentrations and loads...

                    If chkAddMissing.Checked Then

                        'fix up flows...

                        With .Item(FlowColumn, r)
                            If flow = -1 Then
                                If GetMissing(FlowColumn, r) Then 'assign average of values before and after period
                                    .Style = styAverage
                                    flow = .Value
                                Else 'take from station average if possible
                                    flow = dictStationStats(StationID)("Flow")(enumStatType.Conc, DateTime.Month).Avg
                                    .Value = flow
                                    If dictStationStats(StationID)("Flow")(enumStatType.Conc, DateTime.Month).Source = clsStats.enumSource.Defaults Then
                                        .Style = styDefaults
                                    Else
                                        .Style = styMonthly
                                    End If
                                End If
                            Else
                                .Style = styAllGood
                            End If

                            'check for outliers on flow
                            Dim tol, min, max As Double
                            With dictStationStats(StationID)("Flow")(enumStatType.Conc, DateTime.Month)
                                tol = .StdDev * Val(txtOutlier.Text)
                                min = .Avg - tol
                                max = .Avg + tol
                            End With
                            If chkOutlier.Checked And tol > 0 And (flow < min Or flow > max) Then
                                .Style = styOutlier
                                some_outliers = True
                            End If
                        End With

                        'do remainder of parameters

                        For c As Integer = FlowColumn + 1 To .ColumnCount - 1 Step 2
                            If Not .Columns(c).Visible Then Continue For
                            Dim PCodeField As String = .Columns(c).Name.Replace("-Conc", "").Replace("-", "_").Replace(" ", "_")
                            Dim statMonth As clsStats = dictStationStats(StationID)(PCodeField)(enumStatType.Conc, DateTime.Month)
                            Dim statAnnual As clsStats = dictStationStats(StationID)(PCodeField)(enumStatType.Conc, 0)

                            .Item(c, r).Style = styNormal
                            Dim isEstimated As Boolean = False

                            Dim conc As Double = TestNull(.Item(c, r).Value, -1)
                            Dim load As Double = TestNull(.Item(c + 1, r).Value, -1)

                            'if conc is missing or zero, try to estimate from prior and/or next; otherwise will take from statistics

                            With .Item(c, r)
                                If conc = -1 Or conc = 0 Then 'estimate missing conc

                                    If load > 0 And flow > 0 Then 'back-calculate from load and flow
                                        conc = load / flow / Mult
                                        .Value = conc
                                        .Style = styComputed
                                    ElseIf GetMissing(c, r) Then
                                        conc = .Value
                                        .Value = conc
                                        .Style = styAverage
                                    Else 'get from monthly average or defaults

                                        'if no found values at all, take from defaults
                                        If statAnnual.Items.Count = 0 Then 'no data available for this parameter, use conc from defaults
                                            Dim foundDefaultRow As DataRow = dsLSPC.Tables("WRDB_Defaults").Rows.Find(PCodeField.Replace("_", "-"))
                                            If foundDefaultRow Is Nothing Then
                                                .Style = styMissing
                                            Else
                                                conc = TestNull(foundDefaultRow.Item(DateTime.Month), 0)
                                                .Value = conc
                                                .Style = styDefaults
                                            End If
                                        Else
                                            conc = statMonth.Avg
                                            .Value = conc
                                            .Style = styMonthly
                                        End If
                                    End If
                                End If

                                'see if is outlier
                                Dim tol As Double = statAnnual.StdDev * Val(txtOutlier.Text)
                                If chkOutlier.Checked And statMonth.StdDev > 0 And (conc < statMonth.Avg - tol Or conc > statMonth.Avg + tol) Then
                                    .Style = styOutlier
                                    some_outliers = True
                                End If
                            End With

                            'estimate missing load if necessary

                            With .Item(c + 1, r)
                                If load = -1 Or load = 0 Then
                                    If flow = 0 Then
                                        .Value = 0
                                        .Style = styZeroFlow
                                    Else
                                        If GetMissing(c + 1, r) Then 'try to get average of before and after
                                            .Style = styAverage
                                        Else 'try to estimate from conc
                                            load = conc * flow * Mult
                                            .Value = load
                                            .Style = styComputed
                                        End If
                                    End If
                                End If

                                'see if is outlier
                                statMonth = dictStationStats(StationID)(PCodeField)(enumStatType.Load, DateTime.Month)
                                Dim tol As Double = statMonth.StdDev * Val(txtOutlier.Text)
                                If chkOutlier.Checked And statMonth.StdDev > 0 And (load < statMonth.Avg - tol Or load > statMonth.Avg + tol) Then
                                    .Style = styOutlier
                                    some_outliers = True
                                End If
                            End With
                        Next c

                    Else 'not filling in missing values; just flag flows and loads
                        If .Item(FlowColumn, r).Value Is Nothing Then .Item(FlowColumn, r).Style = styMissing
                        For c As Integer = FlowColumn + 2 To .ColumnCount - 1 Step 2
                            If .Item(c, r).Value Is Nothing Then .Item(c, r).Style = styMissing
                        Next
                    End If

                    'set tooltips and write warnings to file

                    For c As Integer = FlowColumn To .ColumnCount - 1
                        With .Item(c, r)
                            If .Style Is styAllGood Then
                                .ToolTipText = ""
                                lblLegendFound.Visible = True
                            ElseIf .Style Is styAverage Then
                                .ToolTipText = "Missing value was estimated from average of values before and after"
                                lblLegendAverage.Visible = True
                            ElseIf .Style Is styMissing Then
                                .ToolTipText = "Missing value; no data found"
                                lblLegendMissing.Visible = True
                            ElseIf .Style Is styDefaults Then
                                .ToolTipText = "Missing concentration was taken from default concentration"
                                lblLegendDefaults.Visible = True
                            ElseIf .Style Is styMonthly Then
                                .ToolTipText = "Missing value was estimated from monthly average"
                                lblLegendMonthly.Visible = True
                            ElseIf .Style Is styZeroFlow Then
                                .ToolTipText = "Load set to zero because flow is zero"
                                lblLegendZeroFlow.Visible = True
                            ElseIf .Style Is styComputed Then
                                .ToolTipText = "Missing value was calculated from available concentration or load and flow"
                                lblLegendComputed.Visible = True
                            ElseIf .Style Is styOutlier Then
                                .ToolTipText = "This value was determined to be an outlier; please check"
                                lblLegendOutlier.Visible = True
                            Else
                                .ToolTipText = ""
                            End If
                            If sw IsNot Nothing And .ToolTipText <> "" Then sw.WriteLine(String.Format("{0:d}: {3} for {1} at station {2}.", DateTime, .OwningColumn.Name, StationID, .ToolTipText))
                        End With
                    Next

                    'now do complex speciation checks; rules are as follows:
                    'Assume A & B are speciation components, C is total; assume "found" means not estimated:
                    '1. If A & B found, set C = A + B
                    '2. If C found and A or B found, set A=C-B or B=C-A
                    '3. If C found and A and B not found, set A=PctA * C and B=PctB * C
                    '4. If A, B, C not found, repeat steps 1 through 3 except "found" means "not default"
                    '5. If non of above apply, C = A + B

                    'do Phosphorus first
                    Dim colPOrganConc As String = cboPOrgan.Text & "-Conc"
                    Dim colPOrthoConc As String = cboPOrtho.Text & "-Conc"
                    Dim colPTotalConc As String = cboPTotal.Text & "-Conc"
                    Dim colPOrganLoad As String = cboPOrgan.Text & "-Load"
                    Dim colPOrthoLoad As String = cboPOrtho.Text & "-Load"
                    Dim colPTotalLoad As String = cboPTotal.Text & "-Load"

                    Dim col As New Generic.List(Of String)
                    Dim msg As String = ""
                    Dim value As New Generic.List(Of Double)

                    If chkAddMissing.Checked And .Columns(colPOrganConc).Visible And .Columns(colPOrthoConc).Visible And .Columns(colPTotalConc).Visible Then

                        If IsFound(r, colPOrganConc, colPOrganLoad) And IsFound(r, colPOrthoConc, colPOrthoLoad) Then 'A & B conc or load found, Compute C
                            col.Add(colPTotalConc)
                            value.Add(.Item(colPOrganConc, r).Value + .Item(colPOrthoConc, r).Value)
                            msg = "Speciation override; total = sum of parts (totalled)"
                        ElseIf IsFound(r, colPTotalConc, colPTotalLoad) Then 'C found, compute A and/or B
                            If IsFound(r, colPOrganConc, colPOrganLoad) Then 'A found, compute B
                                col.Add(colPOrthoConc)
                                value.Add(.Item(colPTotalConc, r).Value - .Item(colPOrganConc, r).Value)
                                msg = "Speciation override; total = sum of parts (back-calculated)"
                            ElseIf IsFound(r, colPOrganConc, colPOrganLoad) Then 'compute A
                                col.Add(colPOrganConc)
                                value.Add(.Item(colPTotalConc, r).Value - .Item(colPOrthoConc, r).Value)
                                msg = "Speciation override; total = sum of parts (back-calculated)"
                            Else 'compute A & B by using percentages of C
                                col.Add(colPOrthoConc)
                                value.Add(.Item(colPTotalConc, r).Value * Val(txtPOrtho.Text) / 100.0)
                                col.Add(colPOrganConc)
                                value.Add(.Item(colPTotalConc, r).Value * Val(txtPOrgan.Text) / 100.0)
                                msg = "Speciation override; total = sum of parts (percentages)"
                            End If
                        Else 'only A or B or neither found, use above rules again, but this time based on non-default rather than found 
                            If IsNonDefault(r, colPOrganConc) And IsNonDefault(r, colPOrthoConc) Then 'A & B conc or load found, Compute C
                                col.Add(colPTotalConc)
                                value.Add(.Item(colPOrganConc, r).Value + .Item(colPOrthoConc, r).Value)
                                msg = "Speciation override for non-default; total = sum of parts (totalled)"
                            ElseIf IsNonDefault(r, colPTotalConc) Then 'C found, compute A and/or B
                                If IsNonDefault(r, colPOrganConc) Then 'A found, compute B
                                    col.Add(colPOrthoConc)
                                    value.Add(.Item(colPTotalConc, r).Value - .Item(colPOrganConc, r).Value)
                                    msg = "Speciation override for non-default; total = sum of parts (back-calculated)"
                                ElseIf IsNonDefault(r, colPOrganConc) Then 'compute A
                                    col.Add(colPOrganConc)
                                    value.Add(.Item(colPTotalConc, r).Value - .Item(colPOrthoConc, r).Value)
                                    msg = "Speciation override for non-default; total = sum of parts (back-calculated)"
                                Else 'compute A & B by using percentages of C
                                    col.Add(colPOrthoConc)
                                    value.Add(.Item(colPTotalConc, r).Value * Val(txtPOrtho.Text) / 100.0)
                                    col.Add(colPOrganConc)
                                    value.Add(.Item(colPTotalConc, r).Value * Val(txtPOrgan.Text) / 100.0)
                                    msg = "Speciation override for non-default; total = sum of parts (percentages)"
                                End If
                            Else 'only A or B or neither found, use A + B = C
                                col.Add(colPTotalConc)
                                value.Add(.Item(colPOrganConc, r).Value + .Item(colPOrthoConc, r).Value)
                                msg = "Speciation override for non-default; total = sum of parts (using estimates)"
                            End If
                        End If

                        For i As Integer = 0 To col.Count - 1
                            'conc column
                            With .Item(col(i), r)
                                .Value = value(i)
                                .Style = stySpeciation
                                .ToolTipText = msg
                                If sw IsNot Nothing Then sw.WriteLine(String.Format("{0:d}: {3} for {1} at station {2}.", DateTime, col(i), StationID, .ToolTipText))
                            End With
                            'load column
                            With .Item(col(i).Replace("-Conc", "-Load"), r)
                                .Value = value(i) * flow * Mult
                                .Style = stySpeciation
                                .ToolTipText = msg & "; load from flow and conc"
                                If sw IsNot Nothing Then sw.WriteLine(String.Format("{0:d}: {3} for {1} at station {2}.", DateTime, col(i).Replace("-Conc", "-Load"), StationID, .ToolTipText))
                            End With
                            lblLegendSpeciation.Visible = True
                        Next
                    End If

                    'do Nitrogen next

                    Dim colNH3Conc As String = cboNH3.Text & "-Conc"
                    Dim colNOrganConc As String = cboNOrgan.Text & "-Conc"
                    Dim colNOXConc As String = cboNOX.Text & "-Conc"
                    Dim colNTotalConc As String = cboNTotal.Text & "-Conc"
                    Dim colNH3Load As String = cboNH3.Text & "-Load"
                    Dim colNOrganLoad As String = cboNOrgan.Text & "-Load"
                    Dim colNOXLoad As String = cboNOX.Text & "-Load"
                    Dim colNTotalLoad As String = cboNTotal.Text & "-Load"

                    col = New Generic.List(Of String)
                    msg = ""
                    value = New Generic.List(Of Double)

                    If chkAddMissing.Checked And .Columns(colNH3Conc).Visible And .Columns(colNOrganConc).Visible And .Columns(colNOXConc).Visible And .Columns(colNTotalConc).Visible Then

                        If IsFound(r, colNH3Conc, colNH3Load) And IsFound(r, colNOrganConc, colNOrganLoad) And IsFound(r, colNOXConc, colNOXLoad) Then 'A & B & C found, Compute D
                            col.Add(colNTotalConc)
                            value.Add(.Item(colNH3Conc, r).Value + .Item(colNOrganConc, r).Value + .Item(colNOXConc, r).Value)
                            msg = "Speciation override; total = sum of parts (totalled)"
                        ElseIf IsFound(r, colNTotalConc, colNTotalLoad) Then 'D found, compute A and/or B and/or C from the other two
                            If IsFound(r, colNH3Conc, colNH3Load) And IsFound(r, colNOrganConc, colNOrganLoad) Then 'A and B found, compute C
                                col.Add(colNOXConc)
                                value.Add(.Item(colNTotalConc, r).Value - .Item(colNH3Conc, r).Value - .Item(colNOrganConc, r).Value)
                                msg = "Speciation override; total = sum of parts (back-calculated)"
                            ElseIf IsFound(r, colNH3Conc, colNH3Load) And IsFound(r, colNOXConc, colNOXLoad) Then
                                col.Add(colNOrganConc)
                                value.Add(.Item(colNTotalConc, r).Value - .Item(colNH3Conc, r).Value - .Item(colNOXConc, r).Value)
                                msg = "Speciation override; total = sum of parts (back-calculated)"
                            ElseIf IsFound(r, colNOrganConc, colNOrganLoad) And IsFound(r, colNOXConc, colNOXLoad) Then
                                col.Add(colNH3Conc)
                                value.Add(.Item(colNTotalConc, r).Value - .Item(colNOrganConc, r).Value - .Item(colNOXConc, r).Value)
                                msg = "Speciation override; total = sum of parts (back-calculated)"
                            Else 'compute A & B & C
                                col.Add(colNH3Conc)
                                value.Add(.Item(colNTotalConc, r).Value * Val(txtNH3.Text) / 100.0)
                                col.Add(colNOrganConc)
                                value.Add(.Item(colNTotalConc, r).Value * Val(txtNOrgan.Text) / 100.0)
                                col.Add(colNOXConc)
                                value.Add(.Item(colNTotalConc, r).Value * Val(txtNOX.Text) / 100.0)
                                msg = "Speciation override; total = sum of parts (percentages)"
                            End If
                        Else 'only A or B or C or none found, repeat above rules using non-default test instead of found test
                            If IsNonDefault(r, colNH3Conc) And IsNonDefault(r, colNOrganConc) And IsNonDefault(r, colNOXConc, colNOXLoad) Then 'A & B & C found, Compute D
                                col.Add(colNTotalConc)
                                value.Add(.Item(colNH3Conc, r).Value + .Item(colNOrganConc, r).Value + .Item(colNOXConc, r).Value)
                                msg = "Speciation override for non-default; total = sum of parts (totalled)"
                            ElseIf IsNonDefault(r, colNTotalConc) Then 'D found, compute A and/or B and/or C from the other two
                                If IsNonDefault(r, colNH3Conc) And IsNonDefault(r, colNOrganConc) Then 'A and B found, compute C
                                    col.Add(colNOXConc)
                                    value.Add(.Item(colNTotalConc, r).Value - .Item(colNH3Conc, r).Value - .Item(colNOrganConc, r).Value)
                                    msg = "Speciation override for non-default; total = sum of parts (back-calculated)"
                                ElseIf IsNonDefault(r, colNH3Conc) And IsNonDefault(r, colNOXConc) Then
                                    col.Add(colNOrganConc)
                                    value.Add(.Item(colNTotalConc, r).Value - .Item(colNH3Conc, r).Value - .Item(colNOXConc, r).Value)
                                    msg = "Speciation override for non-default; total = sum of parts (back-calculated)"
                                ElseIf IsNonDefault(r, colNOrganConc) And IsNonDefault(r, colNOXConc) Then
                                    col.Add(colNH3Conc)
                                    value.Add(.Item(colNTotalConc, r).Value - .Item(colNOrganConc, r).Value - .Item(colNOXConc, r).Value)
                                    msg = "Speciation override for non-default; total = sum of parts (back-calculated)"
                                Else 'compute A & B & C
                                    col.Add(colNH3Conc)
                                    value.Add(.Item(colNTotalConc, r).Value * Val(txtNH3.Text) / 100.0)
                                    col.Add(colNOrganConc)
                                    value.Add(.Item(colNTotalConc, r).Value * Val(txtNOrgan.Text) / 100.0)
                                    col.Add(colNOXConc)
                                    value.Add(.Item(colNTotalConc, r).Value * Val(txtNOX.Text) / 100.0)
                                    msg = "Speciation override for non-default; total = sum of parts (percentages)"
                                End If
                            Else 'D is default and only some (or none) of A, B, or C found as non-default; use whatever I have to calculate total
                                col.Add(colNTotalConc)
                                value.Add(.Item(colNH3Conc, r).Value + .Item(colNOrganConc, r).Value + .Item(colNOXConc, r).Value)
                                msg = "Speciation override for partial non-default; total = sum of parts (totalled)"
                            End If
                        End If

                        For i As Integer = 0 To col.Count - 1
                            'conc column
                            With .Item(col(i), r)
                                .Value = value(i)
                                .Style = stySpeciation
                                .ToolTipText = msg
                                If sw IsNot Nothing Then sw.WriteLine(String.Format("{0:d}: {3} for {1} at station {2}.", DateTime, col(i), StationID, .ToolTipText))
                            End With
                            'load column
                            With .Item(col(i).Replace("-Conc", "-Load"), r)
                                .Value = value(i) * flow * Mult
                                .Style = stySpeciation
                                .ToolTipText = msg & "; load from flow and conc"
                                If sw IsNot Nothing Then sw.WriteLine(String.Format("{0:d}: {3} for {1} at station {2}.", DateTime, col(i).Replace("-Conc", "-Load"), StationID, .ToolTipText))
                            End With
                            lblLegendSpeciation.Visible = True
                        Next
                    End If

                    If Not MainForm.WriteProgress(r, .RowCount - 1, "Applying table style...", ProgressBarStyle.Blocks, True) Then Exit Sub
                Next r
                .CurrentCell = .Item(0, 0)
                .ShowCellToolTips = True

            End With

            If some_outliers Then
                lblOutliersFound.Visible = True
            End If

        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            If sw IsNot Nothing Then
                sw.Close()
                sw.Dispose()
            End If
            lnkWarnings.Visible = True
            MainForm.ProgressComplete()
        End Try
    End Sub

    ''' <summary>
    ''' Reserve italic fonts for estimated values (italic and underlined for default values)
    ''' </summary>
    Private Function IsEstimated(ByVal columnIndex As Integer, ByVal rowIndex As Integer) As Boolean
        With dgCrosstabProc
            Return .Item(columnIndex, rowIndex).Style.Font IsNot Nothing AndAlso .Item(columnIndex, rowIndex).Style.Font.Italic
        End With
    End Function

    ''' <summary>
    ''' Reserve italic fonts for estimated values (italic and underlined for default values)
    ''' </summary>
    Private Function IsEstimated(ByVal columnName As String, ByVal rowIndex As Integer) As Boolean
        With dgCrosstabProc
            Return .Item(columnName, rowIndex).Style.Font IsNot Nothing AndAlso .Item(columnName, rowIndex).Style.Font.Italic
        End With
    End Function

    ''' <summary>
    ''' Reserve italic fonts for estimated values (italic and underlined for default values)
    ''' Check multiple columns and return true if any are not estimated
    ''' </summary>
    Private Function IsFound(ByVal rowIndex As Integer, ByVal ParamArray columnNames() As String) As Boolean
        With dgCrosstabProc
            For Each columnName As String In columnNames
                If Not IsEstimated(columnName, rowIndex) Then Return True
            Next
            Return False
        End With
    End Function

    ''' <summary>
    ''' Reserve italic and underlined for default values
    ''' Check multiple columns and return true if any are not default
    ''' </summary>
    Private Function IsNonDefault(ByVal rowIndex As Integer, ByVal ParamArray columnNames() As String) As Boolean
        With dgCrosstabProc
            For Each columnName As String In columnNames
                If .Item(columnName, rowIndex).Style.Font IsNot Nothing AndAlso Not .Item(columnName, rowIndex).Style.Font.Underline Then Return True
            Next
            Return False
        End With
    End Function

    Private Function GetPrior(ByVal columnIndex As Integer, ByVal rowIndex As Integer, ByRef NumMissing As Integer) As Single
        With dgCrosstabRaw
            Dim StationID As String = .Item("Station_ID", rowIndex).Value
            For r As Integer = rowIndex - 1 To 0 Step -1
                If .Item("Station_ID", r).Value = StationID Then
                    Dim isEstimated As Boolean = (.Item(columnIndex, r).Style.Font IsNot Nothing AndAlso .Item(columnIndex, r).Style.Font.Italic)
                    If TestNull(.Item(columnIndex, r).Value, "").ToString <> "" And Not isEstimated Then NumMissing = rowIndex - r - 1 : Return .Item(columnIndex, r).Value
                Else
                    NumMissing = rowIndex - r - 1
                    Return -1
                End If
            Next
            NumMissing = rowIndex - 1
            Return -1
        End With
    End Function

    Private Function GetNext(ByVal columnIndex As Integer, ByVal rowIndex As Integer, ByRef NumMissing As Integer) As Single
        With dgCrosstabRaw
            Dim StationID As String = .Item("Station_ID", rowIndex).Value
            For r As Integer = rowIndex + 1 To .RowCount - 1
                If .Item("Station_ID", r).Value = StationID Then
                    If TestNull(.Item(columnIndex, r).Value, "").ToString <> "" Then NumMissing = r - rowIndex - 1 : Return .Item(columnIndex, r).Value
                Else
                    NumMissing = r - rowIndex - 1
                    Return -1
                End If
            Next
            NumMissing = .RowCount - rowIndex - 1
            Return -1
        End With
    End Function

    ''' <summary>
    ''' Search for prior and next non-missing data and estimate; returns True if can estimate, False if no data found or too many missing
    ''' </summary>
    Private Function GetMissing(ByVal columnIndex As Integer, ByVal rowIndex As Integer) As Boolean
        With dgCrosstabProc
            Dim Num1 As Integer = 0, Num2 As Integer = 0
            Dim val1 As Single = GetPrior(columnIndex, rowIndex, Num1)
            Dim val2 As Single = GetNext(columnIndex, rowIndex, Num2)
            If val1 = -1 Or val2 = -1 Or Num1 + Num2 + 1 > txtMaxMissing.Text Then
                Return False
            Else
                With .Item(columnIndex, rowIndex)
                    If val1 = -1 And val2 = -1 Then
                        Return False
                    ElseIf val1 = -1 Then
                        .Value = val2
                        Return True
                    ElseIf val2 = -1 Then
                        .Value = val1
                        Return True
                    Else
                        .Value = (val1 + val2) / 2
                        Return True
                    End If
                End With
            End If
        End With
    End Function

    Private Sub dgStations_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgStations.Validated
        Try
            Dim dt As DataTable = dsLSPC.Tables("WRDB_Stations")
            If dt.GetChanges IsNot Nothing Then
                Dim numrows As Integer = daStations.Update(dt)
                dt.AcceptChanges()
            End If
        Catch ex As Exception
            ErrorMsg("Unable to update Access database table; please close tool and reopen to try again.", ex)
        End Try
    End Sub

    Private Sub dgPCodes_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgPCodes.Validated
        Try
            Dim dt As DataTable = dsLSPC.Tables("WRDB_PCodes")
            If dt.GetChanges IsNot Nothing Then 'AndAlso WarningMsg("The PCodes table has changed; do you want to save the changes?") = Windows.Forms.DialogResult.OK Then
                Dim numrows As Integer = daPCodes.Update(dt)
                dt.AcceptChanges()
            End If
        Catch ex As Exception
            ErrorMsg("Unable to update Access database table; please close tool and reopen to try again.", ex)
        End Try
    End Sub

    Private Sub dg_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgDefaults.CellValueChanged, dgStations.CellValueChanged, dgPCodes.CellValueChanged
        CrossTabRawNeedsUpdate = True
    End Sub

    Private Sub dgDefaults_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgDefaults.Validated
        Try
            Dim dt As DataTable = dsLSPC.Tables("WRDB_Defaults")
            If dt.GetChanges IsNot Nothing Then 'AndAlso WarningMsg("The PCodes table has changed; do you want to save the changes?") = Windows.Forms.DialogResult.OK Then
                Dim numrows As Integer = daDefaults.Update(dt)
                dt.AcceptChanges()
            End If
        Catch ex As Exception
            ErrorMsg("Unable to update Access database table; please close tool and reopen to try again.", ex)
        End Try
    End Sub

    Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
        If WarningMsg("Are you sure you want to reset the contents of all WRDB_ tables stored in the LSPC database?") = Windows.Forms.DialogResult.OK Then Reset()
    End Sub

    Private Sub btnTimeseries_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnTimeseries.Click
        Try
            TabMain.Enabled = False
            Application.UseWaitCursor = True
            If dgCrosstabProc.RowCount = 0 Then
                WarningMsg("You must click the 'Generate Preview' button and evaluate you data in the Crosstab tab before you can create the timeseries.")
                Exit Sub
            End If

            CreateTimeseries()

            If WarningMsgFmt("A total of {0:N0} records have been created and placed in the table. Do you want to upload the timeseries to the table WRDB_PS_Timeseries in the LSPC database?", dsLSPC.Tables("WRDB_PS_Timeseries").Rows.Count) = Windows.Forms.DialogResult.OK Then
                DB.SaveTable("WRDB_PS_Timeseries", dsLSPC.Tables("WRDB_PS_Timeseries"))
            End If

        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            TabMain.Enabled = True
            MainForm.ProgressComplete()
            Application.UseWaitCursor = False
        End Try
    End Sub

    Private Sub CreateTimeseries()
        'create dictionaries so can quickly substitute mapped values
        Dim dictStations As New Generic.Dictionary(Of String, Integer)
        For Each r As DataRow In dsLSPC.Tables("WRDB_Stations").Rows
            Dim StationID As Object = r.Item("Station_ID")
            If IsDBNull(StationID) OrElse String.IsNullOrEmpty(StationID) OrElse dictStations.ContainsKey(StationID) Then
                WarningMsg("Each row on the Stations tab must have a unique Station ID entered.")
                Exit Sub
            Else
                dictStations.Add(r.Item("Station_ID"), TestNull(r.Item("Subbasin"), 100))
            End If
        Next

        Dim dictParms As New Generic.Dictionary(Of String, String())
        For Each r As DataRow In dsLSPC.Tables("ModelParm").Rows
            If Not IsDBNull(r.Item("LABEL")) AndAlso Not String.IsNullOrEmpty(r.Item("LABEL")) Then
                dictParms.Add(r.Item("LABEL"), New String() {r.Item("PARM"), r.Item("PARMNAME")})
            End If
        Next

        Dim dt As DataTable = dsLSPC.Tables("WRDB_PS_Timeseries")

        'disable following so user can append additional timeseries (must explicitly clear prior)

        'dt.Rows.Clear()
        'dgTimeseries.Update()

        With dgTimeseries
            For c As Integer = 5 To 6
                With .Columns(c).DefaultCellStyle
                    .Alignment = DataGridViewContentAlignment.MiddleRight
                    .Format = "N3"
                End With
            Next
            .Columns(7).DefaultCellStyle.Format = MyDateFormat()
        End With

        With dgCrosstabProc
            Dim FlowColumn As Integer = 5

            Dim lstMissingStations As New Generic.List(Of String)

            For r As Integer = 0 To .RowCount - 2
                Dim flow As Double = Val(TestNull(.Item(FlowColumn, r).Value, 0)) * txtFlowMult.Text
                For c As Integer = FlowColumn + 2 To .ColumnCount - 1 Step 2
                    If Not .Columns(c).Visible Then Continue For
                    Dim StationID As String = .Item("Station_ID", r).Value
                    Dim foundStationRow As DataRow = dsLSPC.Tables("WRDB_Stations").Rows.Find(StationID)
                    Dim PCodeField As String = .Columns(c).Name.Replace("-Load", "").Replace("-", "_").Replace(" ", "_")
                    If dsLSPC.Tables("WRDB_Stations").Columns.Contains(PCodeField) Then
                        If foundStationRow Is Nothing Then
                            If Not lstMissingStations.Contains(StationID) Then lstMissingStations.Add(StationID)
                        Else
                            If foundStationRow.Item(PCodeField) Then 'parameter is checked for this station
                                Dim load As Double = TestNull(.Item(c, r).Value, 0) * txtLoadMult.Text
                                Dim parm() As String = dictParms(.Columns(c).HeaderText.Replace("-Load", ""))
                                Dim datetime As Date = .Item("Date_Time", r).Value
                                If chkStairstep.Checked Then
                                    Dim datetime1 As Date = New Date(datetime.Year, datetime.Month, 1)
                                    Dim datetime2 As Date = datetime1.AddMonths(1).AddDays(-1)
                                    dt.Rows.Add(.Item(0, r).Value, .Item(2, r).Value, .Item(3, r).Value, parm(0), parm(1), flow, load, datetime1)
                                    dt.Rows.Add(.Item(0, r).Value, .Item(2, r).Value, .Item(3, r).Value, parm(0), parm(1), flow, load, datetime2)
                                Else
                                    dt.Rows.Add(.Item(0, r).Value, .Item(2, r).Value, .Item(3, r).Value, parm(0), parm(1), flow, load, datetime)
                                End If
                            End If
                        End If
                    End If
                Next
                If Not MainForm.WriteProgress(r, .RowCount - 1, "Creating timeseries...", ProgressBarStyle.Blocks, True) Then Exit For
            Next
            MainForm.ProgressComplete()
            If lstMissingStations.Count > 0 Then
                Dim s As String = ""
                For Each sta As String In lstMissingStations
                    s &= sta & vbCr
                Next
                If WarningMsgFmt("The following stations were found in the Crosstab table but are not included on the Stations tab so they could not be sent to the Timeseries table:\n\n{0}\nDo you want WRDB to automatically add these stations? If you, you'll still need to select the desired parameters and then recreate the timeseries.", s) = Windows.Forms.DialogResult.OK Then
                    For Each sta As String In lstMissingStations
                        dsLSPC.Tables("WRDB_Stations").Rows.Add(sta)
                    Next
                End If
            End If
        End With
        With dgTimeseries
            .Update()
            .AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
            .PerformLayout()
        End With
    End Sub

    Private Sub btnSort_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSort.Click
        With dgCrosstabProc
            .Sort(New ARowComparer(SortOrder.Ascending))
        End With
        ApplyTableStyles()
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
    End Sub

    Private Sub dgvCrossTab_AfterContextMenu(ByVal sender As Object, ByVal e As ContextMenuEventArgs) Handles dgvCrossTabProc.AfterContextMenu
        If e.ContextMenuType = ContextMenuEventArgs.ContextMenuTypeEnum.Paste Then
            ApplyTableStyles()
        End If
    End Sub

    Private Sub chkAddMissing_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkAddMissing.CheckedChanged
        lblMaxMissing.Enabled = chkAddMissing.Checked
        txtMaxMissing.Enabled = chkAddMissing.Checked
        chkOutlier.Enabled = chkAddMissing.Checked
        CrossTabRawNeedsUpdate = True
    End Sub

    Private Sub lnkWarnings_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkWarnings.Click
        If My.Computer.FileSystem.FileExists(clsProject.TempPath & "\LSPC Export.txt") Then Process.Start(clsProject.TempPath & "\LSPC Export.txt")
    End Sub

    Private Sub btnRefresh_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRefresh.Click
        ApplyTableStyles()
    End Sub

    Private Sub TabMain_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabMain.SelectedIndexChanged
        If TabMain.SelectedIndex = 2 Then
            dgSummary.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
        ElseIf TabMain.SelectedIndex = 3 Then
            For c As Integer = 2 To dgStations.ColumnCount - 2
                dgStations.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            Next
            dgStations.Columns(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            dgStations.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            dgvStations.AutoResizeColumnsEqual(2, dgStations.ColumnCount - 1)
        ElseIf TabMain.SelectedIndex = 6 Then
            dgvDefaults.AutoResizeColumnsEqual(1, dgDefaults.ColumnCount - 1)
        ElseIf TabMain.SelectedIndex = 7 Then
            If TabPreview.SelectedIndex = 1 Or TabPreview.SelectedIndex = 2 Then
                If CrossTabRawNeedsUpdate Then CreateCrossTabRaw()
            ElseIf TabPreview.SelectedIndex = 3 Then
                If CrossTabProcNeedsUpdate Then CreateCrossTabProc()
            End If
        End If
    End Sub

    Private Sub btnClearGrid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClearGrid.Click
        Dim dt As DataTable = dsLSPC.Tables("WRDB_PS_Timeseries")
        dt.Rows.Clear()
        dgTimeseries.Update()
    End Sub

    Private Sub btnClearTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClearTable.Click
        If WarningMsg("Are you sure you want to delete all records in the WRDB_PS_Timeseries table in the LSPC database?") = Windows.Forms.DialogResult.OK Then
            DB.ExecuteNonQuery("DELETE FROM WRDB_PS_Timeseries")
        End If
    End Sub

    ''' <summary>
    ''' Calculate the statistics for each station and parameter (flow or concentration) to allow substitution when the crosstab
    ''' missing values are assigned. Data are taken from crosstab (raw data only) such that will use conc if available; if not, and
    ''' flow is available, will back-calculate from load and flow. This assumes that the data grid has been filled with raw data 
    ''' but no values have yet been estimated.
    ''' </summary>
    Private Sub CalcStationStats()
        Try
            cboStationID.Items.Clear()
            For Each row As DataRow In dsLSPC.Tables("WRDB_Stations").Rows
                cboStationID.Items.Add(TestNull(row.Item("Station_ID"), ""))
            Next

            If cboStatistic.SelectedIndex = -1 Then cboStatistic.SelectedIndex = 0

            'create lookup by station ID; each contains a dictionary by pcode containing array of monthly stats (first index is conc or load, second is annual or 12 months)
            'reset the dictionary
            dictStationStats = New Generic.Dictionary(Of String, Generic.Dictionary(Of String, clsStats(,)))

            'gather list of possible stations and parameters (already stored in comboboxes)
            For Each StationID As String In cboStationID.Items
                dictStationStats.Add(StationID, New Generic.Dictionary(Of String, clsStats(,)))
                Dim statsF(1, 12) As clsStats 'item 0 is stats for all data; 1 thru 12 are for months
                For i As Integer = enumStatType.Conc To enumStatType.Load
                    For j As Integer = 0 To 12
                        statsF(i, j) = New clsStats
                    Next
                Next
                dictStationStats(StationID).Add("Flow", statsF)
                For Each Parm As String In lstParm
                    Dim stats(1, 12) As clsStats 'item 0 is stats for all data; 1 thru 12 are for months
                    For i As Integer = enumStatType.Conc To enumStatType.Load
                        For j As Integer = 0 To 12
                            stats(i, j) = New clsStats
                        Next
                    Next
                    dictStationStats(StationID).Add(Parm.Replace("-", "_"), stats)
                Next
            Next

            If dictStationStats.Count = 0 Then Exit Sub
            Dim Mult As Double = Val(txtMult.Text)

            With dgCrosstabRaw
                For r As Integer = 0 To .RowCount - 1
                    Dim StationID As String = .Item("Station_ID", r).Value
                    Dim DateTime As Date = .Item("Date_Time", r).Value
                    Dim Flow As Double = TestNull(.Item("Flow", r).Value, Double.MinValue)
                    If Flow <> Double.MinValue Then
                        dictStationStats(StationID)("Flow")(enumStatType.Conc, 0).Items.Add(Flow)
                        dictStationStats(StationID)("Flow")(enumStatType.Conc, DateTime.Month).Items.Add(Flow)
                    End If
                    For c As Integer = 6 To .ColumnCount - 1 Step 2
                        Dim Parm As String = .Columns(c).Name.Replace("-Conc", "")
                        Dim Conc As Double = TestNull(.Item(c, r).Value, Double.MinValue)
                        Dim Load As Double = TestNull(.Item(c + 1, r).Value, Double.MinValue)
                        If Conc <> Double.MinValue Then
                            dictStationStats(StationID)(Parm.Replace("-", "_"))(enumStatType.Conc, 0).Items.Add(Conc)
                            dictStationStats(StationID)(Parm.Replace("-", "_"))(enumStatType.Conc, DateTime.Month).Items.Add(Conc)
                        End If
                        If Load <> Double.MinValue Then
                            dictStationStats(StationID)(Parm.Replace("-", "_"))(enumStatType.Load, 0).Items.Add(Load)
                            dictStationStats(StationID)(Parm.Replace("-", "_"))(enumStatType.Load, DateTime.Month).Items.Add(Load)
                        End If
                    Next
                    MainForm.WriteProgress(r, .RowCount - 1, "Computing station statistics", ProgressBarStyle.Blocks, False)
                Next r
            End With

            'now finish computation of statistics (if no values were found in month or if average is zero, will substitute annual average; if none ever, will substitute defaults)
            For Each s As String In dictStationStats.Keys
                For Each p As String In dictStationStats(s).Keys
                    For t As enumStatType = enumStatType.Conc To enumStatType.Load
                        'will use iterative procedure to identify outliers using annual data and remove them from 
                        CalcStats(dictStationStats(s)(p)(t, 0), clsStats.enumSource.Annual)

                        For i As Integer = 1 To 12 'monthly stats
                            CalcStats(dictStationStats(s)(p)(t, i), clsStats.enumSource.Unknown, dictStationStats(s)(p)(t, 0))
                            With dictStationStats(s)(p)(t, i)
                                If .Items.Count = 0 Or .Avg = 0.0 Then 'no monthly values found, use monthly defaults (or annual if not provided, or is flow or load)
                                    With dsLSPC.Tables("WRDB_Defaults")
                                        .PrimaryKey = New DataColumn() {.Columns(0)}
                                    End With
                                    Dim foundDefaultRow As DataRow = dsLSPC.Tables("WRDB_Defaults").Rows.Find("Flow")
                                    If t = enumStatType.Conc AndAlso p = "Flow" AndAlso foundDefaultRow IsNot Nothing AndAlso Not IsDBNull(foundDefaultRow(i)) Then
                                        .Avg = foundDefaultRow(i)
                                        .Source = clsStats.enumSource.Defaults
                                        .StdDev = 0
                                    Else
                                        .Avg = dictStationStats(s)(p)(t, 0).Avg
                                        .Source = clsStats.enumSource.Annual
                                        .StdDev = 0
                                    End If
                                Else
                                    .Source = clsStats.enumSource.FoundData
                                End If
                            End With
                        Next
                    Next
                Next
            Next

            'note: retrieve stats for January like this: Average = dictStations(StationID)(PCode)(EnumStatType.Conc,1).Avg

            'now set up table for statistics results
            With dgStationStats
                .Columns.Clear()
                .ColumnCount = 14
                For i As Integer = 0 To .ColumnCount - 1
                    With .Columns(i)
                        .SortMode = DataGridViewColumnSortMode.NotSortable
                        .HeaderText = Choose(i + 1, "Parm", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December", "All Data")
                        .Name = .HeaderText
                        If i = 0 Then
                            .ValueType = GetType(String)
                            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                        Else
                            .ValueType = GetType(Double)
                        End If
                    End With
                Next
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToResizeRows = False
                .AllowUserToResizeColumns = False
                .AllowUserToOrderColumns = False
                .ReadOnly = True
                dgvStationDefaults.AutoResizeColumnsEqual(1, .ColumnCount - 1)
            End With

            If cboStationID.Items.Count > 0 Then cboStationID.SelectedIndex = 0
            cboStatType.SelectedIndex = 0
            CrossTabProcNeedsUpdate = True

        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            'causes lock up!
            'If dr IsNot Nothing AndAlso Not dr.IsClosed Then dr.Close()
        End Try
    End Sub

    ''' <summary>
    ''' Iterate until statistics produce no additional outliers; if doing monthly stats, provide annual stats so will use them to determine outliers
    ''' (if try to use iterative solution on monthly, dataset too small and outliers are not detected.)
    ''' </summary>
    Private Sub CalcStats(ByVal stats As clsStats, ByVal source As clsStats.enumSource, Optional ByRef AnnualStats As clsStats = Nothing)
        Dim Mult As Double = txtOutlier.Text
        With stats
            Dim NumOutliers As Integer = 0, PrevNumOutliers As Integer = 0
            Dim MinOutlier As Double = Double.MinValue, MaxOutlier As Double = Double.MaxValue
            If AnnualStats IsNot Nothing Then 'will do single pass for monthly and use outlier values from annual series
                With AnnualStats
                    MinOutlier = .Avg - Mult * .StdDev
                    MaxOutlier = .Avg + Mult * .StdDev
                End With
            End If
            Dim num As Integer = 0
            Do
                PrevNumOutliers = NumOutliers
                num = 0
                .Min = Double.MaxValue
                .Max = Double.MinValue
                Dim sum As Double = 0, sum2 As Double = 0
                For Each v As Double In .Items
                    If v >= MinOutlier And v <= MaxOutlier Then
                        num += 1
                        sum += v
                        sum2 += v * v
                        .Min = Math.Min(.Min, v)
                        .Max = Math.Max(.Max, v)
                    End If
                Next
                If num = 0 Then .Avg = 0 : .Min = 0 : .Max = 0 Else .Avg = sum / num
                If num <= 1 Then .StdDev = 0 Else .StdDev = Math.Sqrt((num * sum2 - sum ^ 2) / (num * (num - 1)))
                MinOutlier = .Avg - Mult * .StdDev
                MaxOutlier = .Avg + Mult * .StdDev
                NumOutliers = 0
                For Each v As Double In .Items
                    If v < MinOutlier Or v > MaxOutlier Then NumOutliers += 1
                Next
            Loop Until NumOutliers = PrevNumOutliers Or num = 2 Or AnnualStats IsNot Nothing
            .NumOutliers = NumOutliers
            .Source = source
        End With
    End Sub

    Private Sub cbo_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboStationID.SelectedValueChanged, cboStatistic.SelectedValueChanged, cboStatType.SelectedValueChanged
        RefreshStationStats()
    End Sub

    Private Sub RefreshStationStats()
        Try
            If cboStationID.Text = "" Or dictStationStats Is Nothing Or cboStatType.Text = "" Then Exit Sub
            With zgStationStats.GraphPane
                .CurveList.Clear()
                .Legend.Position = ZedGraph.LegendPos.BottomCenter
                .Legend.IsVisible = True
                .XAxis.Title.Text = "Month"
                .XAxis.Type = ZedGraph.AxisType.Text
                '.XAxis.Scale.TextLabels = New String() {"Annual", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}
                .XAxis.Scale.TextLabels = New String() {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "All Data"}
                .YAxis.Title.Text = cboStatistic.Text
                .Title.Text = cboStatType.Text & " at " & cboStationID.Text
            End With
            Dim cr As New ZedGraph.ColorSymbolRotator
            dgStationStats.Rows.Clear()
            AddStatRow(cboStationID.Text, enumStatType.Conc, "Flow", cr.NextColor, cr.NextSymbol)
            For Each Parm As String In lstParm
                'only add values if selected parameter is checked
                Dim foundStationRow As DataRow = dsLSPC.Tables("WRDB_Stations").Rows.Find(cboStationID.Text)
                If foundStationRow IsNot Nothing AndAlso TestNull(foundStationRow.Item(Parm.Replace("-", "_")), False) Then
                    Dim StatType As enumStatType = cboStatType.SelectedIndex
                    AddStatRow(cboStationID.Text, StatType, Parm.Replace("-", "_"), cr.NextColor, cr.NextSymbol)
                End If
            Next
            zgStationStats.AxisChange()
            zgStationStats.Refresh()
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub AddStatRow(ByVal StationID As String, ByVal StatType As enumStatType, ByVal Parm As String, ByVal LineColor As Drawing.Color, ByVal Symbol As ZedGraph.SymbolType)
        Dim pts As New ZedGraph.PointPairList
        Dim crv As New ZedGraph.LineItem(Parm)
        With dgStationStats
            .Rows.Add(Parm)
            For c As Integer = 1 To 13
                With dictStationStats(StationID)(Parm)(StatType, IIf(c = 13, 0, c))
                    Dim v As Double = Choose(cboStatistic.SelectedIndex + 1, .Avg, .StdDev, .Min, .Max, .Items.Count, .Avg - .StdDev * Val(txtOutlier.Text), .Avg + .StdDev * Val(txtOutlier.Text), .NumOutliers)
                    dgStationStats.Item(c, dgStationStats.RowCount - 1).Value = v
                    'assign styles to show where values came from if showing averages
                    Dim bk As Color = Color.White
                    Dim tt As String = ""
                    If cboStatistic.SelectedIndex = 0 Then
                        Select Case .Source
                            Case clsStats.enumSource.Unknown : bk = Color.Red : tt = "Unknown value!"
                            Case clsStats.enumSource.Defaults : bk = Color.LightPink : tt = "Global default"
                            Case clsStats.enumSource.FoundData : bk = Color.White
                            Case clsStats.enumSource.Annual : bk = Color.LightBlue : tt = "Annual average"
                        End Select
                    End If
                    If dictStationStats(StationID)(Parm)(StatType, 0).Items.Count = 0 Then
                        bk = Color.LightPink
                        tt = "No data; will use global defaults"
                        If cboStatistic.SelectedIndex = 0 Then dgStationStats.Item(c, dgStationStats.RowCount - 1).Value = DBNull.Value
                    End If
                    dgStationStats.Item(c, dgStationStats.RowCount - 1).Style.BackColor = bk
                    dgStationStats.Item(c, dgStationStats.RowCount - 1).ToolTipText = tt

                    'add curves for graph
                    With dictStationStats(StationID)(Parm)(StatType, IIf(c = 13, 0, c))
                        Dim tol As Double = .StdDev * Val(txtOutlier.Text)
                        pts.Add(c, v)
                    End With
                End With
                .Columns(c).DefaultCellStyle.Format = IIf(cboStatistic.Text.StartsWith("Number"), "N0", "N3")
                .Columns(c).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
                .Columns(c).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            Next
            zgStationStats.GraphPane.CurveList.Add(New ZedGraph.LineItem(Parm, pts, LineColor, Symbol, 1))
        End With
    End Sub

    Private Sub TabPreview_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabPreview.SelectedIndexChanged
        If TabPreview.SelectedIndex = 1 Then
            If CrossTabRawNeedsUpdate Then CreateCrossTabRaw()
            dgCrosstabRaw.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
        ElseIf TabPreview.SelectedIndex = 2 Then
            If CrossTabRawNeedsUpdate Then CreateCrossTabRaw()
            dgvStationDefaults.AutoResizeColumnsEqual(1, dgStationStats.ColumnCount - 1, DataGridViewAutoSizeColumnMode.AllCells)
        ElseIf TabPreview.SelectedIndex = 3 Then
            If CrossTabRawNeedsUpdate Then CreateCrossTabRaw()
            If CrossTabProcNeedsUpdate Then btnPreview.PerformClick()
            dgCrosstabProc.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
        End If
    End Sub

    Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        dgvCrossTabProc.SendToExcel()
    End Sub

    Private Sub txtPOrgan_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPOrgan.Validated
        txtPOrgan.Text = Val(txtPOrgan.Text)
        txtPOrtho.Text = 100 - txtPOrgan.Text
    End Sub

    Private Sub txtPOrtho_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPOrtho.Validated
        txtPOrtho.Text = Val(txtPOrtho.Text)
        txtPOrgan.Text = 100 - txtPOrtho.Text
    End Sub

    Private Sub txtNOrgan_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtNOrgan.Validated
        txtNOrgan.Text = Val(txtNOrgan.Text)
        txtNH3.Text = (100 - txtNOrgan.Text) / 2
        txtNOX.Text = (100 - txtNOrgan.Text) / 2
    End Sub

    Private Sub txtNH3_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtNH3.Validated
        txtNH3.Text = Val(txtNH3.Text)
        txtNOX.Text = 100 - txtNOrgan.Text - txtNH3.Text
    End Sub

    Private Sub txtNOX_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtNOX.Validated
        txtNOX.Text = Val(txtNOX.Text)
        txtNH3.Text = 100 - txtNOrgan.Text - txtNOX.Text
    End Sub

    Private Sub btnOpenMDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenMDB.Click
        Process.Start(_MDBFile)
    End Sub

    Private Sub chk_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkStairstep.CheckedChanged, chkAddMissing.CheckedChanged
        CrossTabRawNeedsUpdate = True
    End Sub

    Private Sub txt_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtMaxMissing.TextChanged, txtOutlier.TextChanged
        CrossTabProcNeedsUpdate = True
    End Sub

    Private Sub dt_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtStartDate.ValueChanged, dtEndDate.ValueChanged
        CrossTabRawNeedsUpdate = True
    End Sub

    Private Sub btnRefreshRaw_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefreshRaw.Click
        CreateCrossTabRaw()
    End Sub

    Private Sub btnRefreshStats_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefreshStats.Click
        CalcStationStats()
    End Sub

    Private Sub chkOutlier_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkOutlier.CheckedChanged
        lblOutlier.Enabled = chkOutlier.Checked
        txtOutlier.Enabled = chkOutlier.Checked
        CrossTabProcNeedsUpdate = True
    End Sub

    Private Sub dgCrosstabRaw_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgCrosstabRaw.CellEndEdit
        CalcStationStats()
        CrossTabProcNeedsUpdate = True
    End Sub

    Private Sub dgCrosstabProc_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgCrosstabProc.CellEndEdit
        CrossTabProcModified = True
    End Sub
End Class

''' <summary>
''' Allow sorting on the first two columns of the crosstab
''' </summary>
''' <remarks></remarks>
Public Class ARowComparer
    Implements System.Collections.IComparer
    Private sortOrderModifier As Integer = 1

    Public Sub New(ByVal sortOrder As SortOrder)
        If sortOrder = sortOrder.Descending Then
            sortOrderModifier = -1
        ElseIf sortOrder = sortOrder.Ascending Then
            sortOrderModifier = 1
        End If
    End Sub

    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare
        Dim DataGridViewRow1 As DataGridViewRow = CType(x, DataGridViewRow)
        Dim DataGridViewRow2 As DataGridViewRow = CType(y, DataGridViewRow)
        ' Try to sort based on the Station column. 
        Dim CompareResult As Integer = System.String.Compare( _
            DataGridViewRow1.Cells(0).Value, _
            DataGridViewRow2.Cells(0).Value)
        ' If the Last Names are equal, sort based on the Date. 
        If CompareResult = 0 Then
            CompareResult = System.DateTime.Compare( _
                DataGridViewRow1.Cells(1).Value, _
                DataGridViewRow2.Cells(1).Value)
        End If
        Return CompareResult * sortOrderModifier
    End Function
End Class

Friend Class clsStats
    Public Enum enumSource
        Unknown
        FoundData
        Annual
        Defaults
    End Enum
    Public Sub New()
        Items = New Generic.List(Of Double)
    End Sub
    Public Items As Generic.List(Of Double)
    Public NumOutliers As Integer
    Public Min As Double
    Public Max As Double
    Public Avg As Double
    Public StdDev As Double
    Public Source As enumSource
End Class